﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetClassDocumentList]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetClassDocumentList];
GO
CREATE PROCEDURE [dbo].[sproc_GetClassDocumentList]
    @ClassID int,
    @UserName nvarchar(255),
    @DisplayType int,
    @OrderType int=0
/*

====================================================
功能:    获得组的文档列表
参数:
    @ClassID int            :    类(组)ID
    @UserName nvarchar(255),    :    用户名
    @DisplayType int,        :    显示类型
    @OrderType int=0        :    排序类型
备注:
    显示类型
            0        :    已归档
            1        :    未归档
            2        :    我的上传
            3        :    已删除
====================================================

*/
AS

SET NOCOUNT ON

--显示已归档文档
IF @displaytype =0
    SELECT a.DocID,a.ClassID,DocTitle,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocApprover) DocApprover,a.DocApproveDate,a.DocViewedTimes,a.DocType,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocAddedBy)  DocAddedBy,convert(nvarchar(10),a.DocAddedDate,120) as DocAddedDate,a.doctype,a.docDeletion,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocLastViewer) DocLastViewer,a.DocLastViewDate
        FROM uds_document a
        WHERE docDeletion=0 and docapproved=1 and doctype=0 and classid = @ClassID 
        ORDER BY DocAddedDate DESC
--显示未归档文档（待审批文档）
ELSE IF @displaytype =1
        SELECT a.DocID,a.ClassID,DocTitle,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocApprover) DocApprover,a.DocApproveDate,a.DocViewedTimes,a.DocType,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocAddedBy)  DocAddedBy,convert(nvarchar(10),a.DocAddedDate,120) as DocAddedDate,a.doctype,a.docDeletion,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocLastViewer) DocLastViewer,a.DocLastViewDate
        FROM uds_document a
        WHERE docDeletion=0 and docapproved=0 and doctype=0 and classid = @ClassID 
        ORDER BY DocAddedDate DESC
--显示我的上传文档
ELSE IF @displaytype =2
        SELECT a.DocID,a.ClassID,DocTitle,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocApprover) DocApprover,a.DocApproveDate,a.DocViewedTimes,a.DocType,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocAddedBy)  DocAddedBy,convert(nvarchar(10),a.DocAddedDate,120) as DocAddedDate,a.doctype,a.docDeletion,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocLastViewer) DocLastViewer,a.DocLastViewDate
        FROM uds_document  a
        WHERE docDeletion=0 and docaddedby = @UserName and doctype=0 and classid = @ClassID 
        ORDER BY DocAddedDate DESC
--显示已删除文档
ELSE IF @displaytype =3
        SELECT a.DocID,a.ClassID,DocTitle,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocApprover) DocApprover,a.DocApproveDate,a.DocViewedTimes,a.DocType,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocAddedBy)  DocAddedBy,convert(nvarchar(10),a.DocAddedDate,120) as DocAddedDate,a.doctype,a.docDeletion,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocLastViewer) DocLastViewer,a.DocLastViewDate
        FROM uds_document  a
        WHERE docDeletion<>0  and doctype=0 and classid = @ClassID 
        ORDER BY DocAddedDate DESC
--显示其他文档
ELSE
        SELECT a.DocID,a.ClassID,DocTitle,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocApprover) DocApprover,a.DocApproveDate,a.DocViewedTimes,a.DocType,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocAddedBy)  DocAddedBy,convert(nvarchar(10),a.DocAddedDate,120) as DocAddedDate,a.doctype,a.docDeletion,a.DocLastViewer,(SELECT RealName FROM UDS_Staff b where b.Staff_Name=a.DocLastViewer) DocLastViewer,a.DocLastViewDate
        FROM uds_document  a
        WHERE 1=2

SET NOCOUNT OFF